/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Entity.Mahasiswa;
import Entity.Prestasi;
import Entity.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import library.Koneksi;

/**
 *
 * @author user
 */
public class DaftarPrestasi {

    Prestasi Prestasi;

    //method untuk menambah prestasi
    public boolean tambahPrestasi(Prestasi Prestasi) {
        boolean hasil = false;
        this.Prestasi = Prestasi;
        Koneksi dbConn = new Koneksi();
        String sql = "insert into prestasi(ID_USER, NAMA_KEG, KATEGORI_KEGIATAN, KETERANGAN_PESERTA, JUMLAH_PESERTA, TAHUN_PRESTASI, PRESTASI_CAPAIAN, TINGKAT, BUKTI_PRESTASI, STATUS_PRESTASI)"
                + " value("
                + "'" + this.Prestasi.getUser().getIdUser() + "',"
                + "'" + this.Prestasi.getNamaKegiatan() + "',"
                + "'" + this.Prestasi.getKategoriKegiatan() + "',"
                + "'" + this.Prestasi.getKeteranganPeserta() + "',"
                + "'" + this.Prestasi.getJumlahPeserta() + "',"
                + "'" + this.Prestasi.getTahunPrestasi() + "',"
                + "'" + this.Prestasi.getPrestasiDicapai() + "',"
                + "'" + this.Prestasi.getTingkat() + "',"
                + "'" + this.Prestasi.getBuktiPrestasi() + "',"
                + "'" + this.Prestasi.getStatusPrestasi() + "'"
                + ")";
        System.out.println("sql : " + sql);
        PreparedStatement st = null;
        try {
            Connection conn = Koneksi.getKoneksi();
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("error : " + ex.getErrorCode());
            System.out.println("error : " + ex.getMessage());
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return hasil;
    }

    public List<Prestasi> ambilDaftarPrestasiMhs(String idUser) {
        List<Prestasi> daftarPrestasi = new ArrayList<Prestasi>();
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT * FROM prestasi, user where prestasi.id_user = user.id_user and user.id_user = '" + idUser + "' order by prestasi.STATUS_PRESTASI";

        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while (rs.next()) {
                this.Prestasi = new Prestasi();
                User user = new User();
                user.setIdUser(rs.getString("ID_USER"));
                user.setNamaUser(rs.getString("NAMA_USER"));
                this.Prestasi.setUser(user);
                this.Prestasi.setIdPrestasi(rs.getInt("ID_PRESTASI"));
                this.Prestasi.setNamaKegiatan(rs.getString("NAMA_KEG"));
                this.Prestasi.setKategoriKegiatan(rs.getString("KATEGORI_KEGIATAN"));
                this.Prestasi.setKeteranganPeserta(rs.getString("KETERANGAN_PESERTA"));
                this.Prestasi.setJumlahPeserta(rs.getString("JUMLAH_PESERTA"));
                this.Prestasi.setTahunPrestasi(rs.getString("TAHUN_PRESTASI"));
                this.Prestasi.setTingkat(rs.getString("TINGKAT"));
                this.Prestasi.setPrestasiDicapai(rs.getString("PRESTASI_CAPAIAN"));
                this.Prestasi.setBuktiPrestasi(rs.getString("BUKTI_PRESTASI"));
                this.Prestasi.setStatusPrestasi(rs.getString("STATUS_PRESTASI"));
                daftarPrestasi.add(this.Prestasi);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return daftarPrestasi;
    }

    public List<Prestasi> ambilRekapPrestasi() {
        List<Prestasi> daftarPrestasi = new ArrayList<Prestasi>();
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT * FROM prestasi where STATUS_PRESTASI = '1'";

        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while (rs.next()) {
                this.Prestasi = new Prestasi();
                User user = new User();
                user.setIdUser(rs.getString("ID_USER"));
                this.Prestasi.setIdPrestasi(rs.getInt("ID_PRESTASI"));
                this.Prestasi.setNamaKegiatan(rs.getString("NAMA_KEG"));
                this.Prestasi.setKategoriKegiatan(rs.getString("KATEGORI_KEGIATAN"));
                this.Prestasi.setKeteranganPeserta(rs.getString("KETERANGAN_PESERTA"));
                this.Prestasi.setJumlahPeserta(rs.getString("JUMLAH_PESERTA"));
                this.Prestasi.setTahunPrestasi(rs.getString("TAHUN_PRESTASI"));
                this.Prestasi.setTingkat(rs.getString("TINGKAT"));
                this.Prestasi.setPrestasiDicapai(rs.getString("PRESTASI_CAPAIAN"));
                this.Prestasi.setBuktiPrestasi(rs.getString("BUKTI_PRESTASI"));
                this.Prestasi.setStatusPrestasi(rs.getString("STATUS_PRESTASI"));
                daftarPrestasi.add(Prestasi);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return daftarPrestasi;
    }

    //method untuk menghapus data prestasi
    public boolean hapusPrestasi(Prestasi prestasi) {

        boolean hasil = true;

        PreparedStatement st = null;
        Koneksi dbConn = new Koneksi();

        String sql = "DELETE FROM prestasi WHERE ID_PRESTASI = " + prestasi.getIdPrestasi();

        Connection conn = Koneksi.getKoneksi();
        try {
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                hasil = false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return hasil;

    }

    //method untuk mengambil data beasiswa dengan id tertentu
    public Prestasi ambilDataPrestasi(String idPrestasi) {

        Koneksi dbConn = new Koneksi();
        String sql = "SELECT * FROM prestasi as A, prestasi as B where A.ID_PRESTASI = B.ID_PRESTASI and A.ID_PRESTASI=" + idPrestasi;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while (rs.next()) {
                this.Prestasi = new Prestasi();
                this.Prestasi.setIdPrestasi(rs.getInt("ID_PRESTASI"));
                User user = new User();
                user.setIdUser(rs.getString("ID_USER"));
                this.Prestasi.setUser(user);
                this.Prestasi.setNamaKegiatan(rs.getString("NAMA_KEG"));
                this.Prestasi.setKategoriKegiatan(rs.getString("KATEGORI_KEGIATAN"));
                this.Prestasi.setKeteranganPeserta(rs.getString("KETERANGAN_PESERTA"));
                this.Prestasi.setJumlahPeserta(rs.getString("JUMLAH_PESERTA"));
                this.Prestasi.setTahunPrestasi(rs.getString("TAHUN_PRESTASI"));
                this.Prestasi.setTingkat(rs.getString("TINGKAT"));
                this.Prestasi.setPrestasiDicapai(rs.getString("PRESTASI_CAPAIAN"));
                this.Prestasi.setBuktiPrestasi(rs.getString("BUKTI_PRESTASI"));
                this.Prestasi.setStatusPrestasi(rs.getString("STATUS_PRESTASI"));

            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return this.Prestasi;

    }

    //   method untuk mengedit beasiswa
    public boolean editPrestasi(Prestasi prestasi) {
        boolean hasil = false;
        this.Prestasi = prestasi;

        String sql = "update prestasi SET "
                + "NAMA_KEG = '" + this.Prestasi.getNamaKegiatan()
                + "', TAHUN_PRESTASI = '" + this.Prestasi.getTahunPrestasi()
                + "', PRESTASI_CAPAIAN = '" + this.Prestasi.getPrestasiDicapai()
                + "', TINGKAT = '" + this.Prestasi.getTingkat()
                + "', BUKTI_PRESTASI = '" + this.Prestasi.getBuktiPrestasi()
                + "', STATUS_PRESTASI = '" + this.Prestasi.getStatusPrestasi()
                + "' where ID_PRESTASI = " + this.Prestasi.getIdPrestasi();



        PreparedStatement st = null;

        try {
            Connection conn = Koneksi.getKoneksi();
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                hasil = false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return hasil;
    }
    
    public List<Prestasi> ListPrestasi(){
        
        PreparedStatement st = null;
        ResultSet result = null;
        List<Prestasi> listPrestasi = new ArrayList<Prestasi>();
        
        try{
            String sql = "Select mahasiswa.*, user.*, COUNT(ID_PRESTASI) as jumlah "
                    + "from Mahasiswa inner join user on Mahasiswa.id_user = user.id_user "
                    + "left join prestasi on mahasiswa.id_user = prestasi.id_user and prestasi.STATUS_PRESTASI = '0' "
                    + "group by mahasiswa.id_user order by jumlah DESC";
            System.out.println(sql);
            Connection conn = Koneksi.getKoneksi();
            st = conn.prepareStatement(sql);
            
            result = st.executeQuery();
            
            while(result.next()){
                Mahasiswa mahasiswa = new Mahasiswa();
                User user = new User();
                user.setIdUser(result.getString("id_user"));
                user.setNamaUser(result.getString("nama_user"));
                
                Prestasi prestasi = new Prestasi();
                prestasi.setIdPrestasi(result.getInt("jumlah"));
                prestasi.setUser(user);
                
                listPrestasi.add(prestasi);
            }
            return listPrestasi;
            
        }catch(SQLException ex){
            System.out.print(ex.getMessage());
            return listPrestasi;            
        }finally{
            if(st != null){
                try{
                    st.close();
                }catch(SQLException ex){
                    
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    
                }
            }
        }
    }
}
